﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data;
using System.Globalization;

namespace HotelManagement.DL.Helper
{
    public abstract class DBHelper
    {
        #region === filed ===
        private const string TALBE_DEFAULT_NAME = "Table";
        private readonly DbConnection m_conDbConnection;
        private DbProviderFactory m_facDbProviderFactory;
        #endregion

        #region === property ===
        public DbProviderFactory DbProviderFactory
        {
            get
            {
                return m_facDbProviderFactory;
            }
            set
            {
                m_facDbProviderFactory = value;
            }
        }

        public string ConnectionString
        {
            get
            {
                return m_conDbConnection.ConnectionString;
            }
        }

        #endregion

        #region === method ===

        #region === constructor ===
        protected DBHelper(DbProviderFactory dbProviderFactory, DbConnection dbConnection) 
        {
            m_facDbProviderFactory = dbProviderFactory;
            m_conDbConnection = dbConnection;
        }

        protected DBHelper()
        { }
        #endregion

        #region === public method ===
        public DbCommand CreateCommandBySql(string sql)
        {
            DbCommand command = m_facDbProviderFactory.CreateCommand();
            command.CommandType = CommandType.Text;
            command.CommandText = sql;
            return command;
        }

        public void AddInParameter(DbCommand command, string name, DbType dbType, object value)
        {
            AddParameter(command, name, dbType, ParameterDirection.Input, String.Empty, DataRowVersion.Default, value);
        }

        public void AddParameter(DbCommand command, string name, DbType dbType, ParameterDirection direction,
                                 string sourceColumn, DataRowVersion sourceVersion, object value)
        {
            DbParameter parDbParameter = null;
            try
            {
                parDbParameter = m_facDbProviderFactory.CreateParameter();
                parDbParameter.ParameterName = name;
                parDbParameter.DbType = dbType;
                parDbParameter.Size = 0;
                parDbParameter.Value = value ?? DBNull.Value;
                parDbParameter.Direction = direction;
                parDbParameter.IsNullable = true;
                parDbParameter.SourceColumn = sourceColumn;
                parDbParameter.SourceVersion = sourceVersion;
                command.Parameters.Add(parDbParameter);
            }
            catch (DbException)
            {
                throw;
            }
            catch (Exception)
            {
                throw;
            }
        }


        public virtual DataSet ExecuteDataSetQuery(DbCommand command)
        {
            //ExecuteDataSetQuery
            DataSet dasDataSet = null;
            try
            {
                dasDataSet = new DataSet();
                dasDataSet.Locale = CultureInfo.InvariantCulture;
                FillDataSet(command, dasDataSet, TALBE_DEFAULT_NAME);
            }
            catch (DbException)
            {
                throw;
            }
            catch (Exception)
            {
                throw;
            }
            return dasDataSet;
        }

        public virtual int ExecuteNonQuery(DbCommand command)
        {
            //ExecuteNonQuery
            int nResult = 0;
            try
            {
                using (DbConnection conn = CreateConnection())
                {
                    command.Connection = conn;
                    nResult = command.ExecuteNonQuery();
                }
            }
            catch (DbException ex)
            {
                throw;
            }
            catch (Exception ex)
            {
                throw;
            }
            return nResult;
        }

        public virtual int ExecuteNonQuery(DbCommand command, DbTransaction trans)
        {
            //ExecuteNonQuery
            int nResult = 0;
            try
            {
                if (trans == null || trans.Connection == null)
                {
                    return nResult;
                }
                command.Transaction = trans;
                command.Connection = trans.Connection;
                if (trans.Connection.State != ConnectionState.Open)
                {
                    trans.Connection.Open();
                }
                nResult = command.ExecuteNonQuery();
            }
            catch (DbException ex)
            {
                throw;
            }
            catch (Exception ex)
            {
                throw;
            }
            return nResult;
        }

        public DbTransaction GetTransaction()
        {
            DbTransaction trans = null;
            DbConnection conn = CreateConnection();
            trans = conn.BeginTransaction();

            return trans;
        }
        #endregion

        #region === private method ===
        protected virtual DbConnection CreateConnection()
        {

            //Create DbConnection
            DbConnection conDbConnection = null;
            try
            {
                conDbConnection = ConnectionManager.CreateConnection();
                conDbConnection.ConnectionString = ConnectionString;
                conDbConnection.Open();
            }
            catch (DbException)
            {
                throw;
            }
            catch (Exception)
            {
                throw;
            }
            return conDbConnection;
        }

        public virtual void FillDataSet(DbCommand command, DataSet dataSet, string tableName)
        {
            using (DbConnection conn = CreateConnection())
            {
                command.Connection = conn;
                CallFillDataSet(command, dataSet, tableName);
            }
        }

        private void CallFillDataSet(IDbCommand command, DataSet dataSet, string tableName)
        {
            using (DbDataAdapter adapter = GetDataAdapter())
            {
                ((IDbDataAdapter)adapter).SelectCommand = command;

                try
                {
                    
                    adapter.TableMappings.Add(tableName, tableName);

                    //fill dataset
                    adapter.Fill(dataSet);
                }
                catch (DbException)
                {
                    throw;
                }
                catch (Exception)
                {
                    throw;
                }

            }
        }

        protected DbDataAdapter GetDataAdapter()
        {
            //Get DbDataAdapter of DbProviderFactory
            DbDataAdapter dapDbDataAdapter = null;
            try
            {
                dapDbDataAdapter = m_facDbProviderFactory.CreateDataAdapter();
                
            }
            catch (DbException)
            {
                throw;
            }
            catch (Exception)
            {
                throw;
            }
            return dapDbDataAdapter;
        }

        #endregion

        #endregion

    }
}
